package cvs;
import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CVSMainPOS {

	public static void main(String[] args) {
		System.out.println("start---");

		// buildCVS(
		// "base_accounts",
		// "select id, c_master_id, c_national_id, name, city,state, zipcode, i_name, i_city, i_state, i_zipcode from t_account where id=c_master_id");

		// buildCVS("usps_zip",
		// "SELECT [zipcode] ,[cityname] ,[stateabbr] ,[streetname] FROM [epos].[dbo].[usps_zip]");

		// buildCVS(
		// "customer_territory_2006",
		// "SELECT * FROM v_customer_territory where period_year=2006 order by period_year, period_month, e_name");
		//
		// buildCVS(
		// "customer_territory_2007",
		// "SELECT * FROM v_customer_territory where period_year=2007 order by period_year, period_month, e_name");
		//
		// buildCVS(
		// "customer_territory_2008",
		// "SELECT * FROM v_customer_territory where period_year=2008 order by period_year, period_month, e_name");
		//
		// buildCVS(
		// "customer_territory_2009",
		// "SELECT * FROM v_customer_territory where period_year=2009 order by period_year, period_month, e_name");
		//
		// buildCVS(
		// "customer_territory_2010",
		// "SELECT * FROM v_customer_territory where period_year=2010 order by period_year, period_month, e_name");

		// tier3_multi();
		// tier3_one();

		// tier2_012010_072010();

		// tier2_01_2007_TO_12_2008_by_base_account();

		// all_account();

		// build_pos_account();
		// build_claimed();

		// build_cpd();
		// build_account();
		// build_t_salesfile();
		// build_t_sales();
		// build_t_source();
		// build_t_product_mapping();
		// build_t_material_master();
		// //
		// tier3_2010();
		// tier2_2010();

		//
		// tier3_2010_bta();
		// tier2_2010_bta();

		//tier3_2010_display();
		buildEDI();

		System.out.println("finished---");

	}

	private static void buildEDI() {
		String sql = "select f.file_name, d.id , d.customer, d.bill_to_name, d.ship_to_name, d.vendor_model, d.customer_model, d.item_quantity, d.qty_type, d.unit_price, d.billing_date from t_edi_data_v2 d, t_edi_file_v2 f where d.file_id = f.id";
		buildCVS("edi_data", sql);
	}

	private static void tier3_2010_display() {
		String sql = CSVGenerator
				.fileToString("D:\\my docs\\sales\\2011\\tier 3 display.sql");
		System.out.println(sql);

		buildCVS("tier 3 display.csv", sql);
	}

	private static void tier2_2010_bta() {
		String sql = CSVGenerator
				.fileToString("D:\\my docs\\sales\\2011\\tier 2 bta.sql");
		System.out.println(sql);

		buildCVS("tier 2 all bta.csv", sql);
	}

	private static void tier3_2010_bta() {
		String sql = CSVGenerator
				.fileToString("D:\\my docs\\sales\\2011\\tier 3 bta.sql");
		System.out.println(sql);

		buildCVS("tier 3 bta except national and etailer.csv", sql);
	}

	private static void tier2_2010() {
		String sql = CSVGenerator
				.fileToString("D:\\my docs\\sales\\2011\\tier 2.sql");
		System.out.println(sql);

		buildCVS("tier 2 all.csv", sql);
	}

	private static void tier3_2010() {
		String sql = CSVGenerator
				.fileToString("D:\\my docs\\sales\\2011\\tier 3.sql");
		System.out.println(sql);

		buildCVS("tier 3 except national and etailer.csv", sql);
	}

	private static void build_cpd() {
		String sql = "select * from COMPANY_FACTDATA where id > 0 ";
		buildCVS("cpd", sql);
	}

	private static void build_account() {
		String sql = "select * from t_account where id > 0 ";
		buildCVS("t_account", sql);
	}

	private static void build_t_salesfile() {
		String sql = "select * from t_salesfile where id > 0 ";
		buildCVS("t_salesfile", sql);
	}

	private static void build_t_sales() {
		String sql = "select * from t_sales where id > 0 ";
		buildCVS("t_sales", sql);
	}

	private static void build_t_source() {
		String sql = "select * from t_source where id > 0 ";
		buildCVS("t_source", sql);
	}

	private static void build_t_product_mapping() {
		String sql = "select * from t_product_mapping where id > 0 ";
		buildCVS("t_product_mapping", sql);
	}

	private static void build_t_material_master() {
		String sql = "select * from t_material_master where id > 0 ";
		buildCVS("t_material_master", sql);
	}

	private static void build_pos_account() {
		String sql = CSVGenerator
				.fileToString("/home/network/pos/statistics/all account with amt.sql");
		System.out.println(sql);

		buildCVS("all account with amt", sql);
	}

	private static void build_claimed() {
		String sql = CSVGenerator
				.fileToString("/home/network/sales/claim/09-2010-list claimed.sql");
		System.out.println(sql);

		buildCVS("09-2010", sql);
	}

	private static void all_account() {
		String sql = CSVGenerator
				.fileToString("C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\epos3\\report\\all account info.sql");
		System.out.println(sql);

		buildCVS("all account info", sql);
	}

	private static void tier2_01_2007_TO_12_2008_by_base_account() {
		String sql = CSVGenerator
				.fileToString("C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\sales2\\report\\tier2_01_2007_TO_12_2008_by_base_account.sql");
		System.out.println(sql);

		buildCVS("tier2_01_2007_TO_12_2008_by_base_account", sql);
	}

	private static void tier2_012010_072010() {
		String sql = CSVGenerator
				.fileToString("C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\Desktop\\sales2\\report\\tier2_01-2010_to_07-2010.sql");
		System.out.println(sql);

		buildCVS("tier2_01-2010_to_07-2010", sql);
	}

	private static void tier3_one() {
		String sql = CSVGenerator
				.fileToString("C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\My Documents\\tier 3 from 01-2009 to 07-2010.sql");
		System.out.println(sql);

		buildCVS("tier 3 from 01-2009 to 07-2010", sql);
	}

	private static void tier3_multi() {
		String sql = CSVGenerator
				.fileToString("C:\\Documents and Settings\\Samsung.SAMSUNG-A8F\\My Documents\\tier 3 from 01-2009 to 07-2010.sql");
		System.out.println(sql);

		buildCVSMulti("tier 3 from 01-2009 to 07-2010", sql);
	}

	public static void buildCVSMulti(String tableName, String sql) {
		System.out.println("table name:" + tableName);
		PreparedStatement ps;
		ResultSet rs;

		DBConnectorReal connector = new DBConnectorReal();

		try {
			connector.start();
			Connection connection = connector.getConnection();

			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			CSVGeneratorMulti generator = new CSVGeneratorMulti("D:\\cvs_out\\"
					+ tableName);
			generator.build(rs);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connector.close();
		}
		System.out.println("end");
	}

	public static void buildCVS(String tableName, String sql) {
		System.out.println("table name:" + tableName);
		PreparedStatement ps;
		ResultSet rs;

		DBConnectorReal connector = new DBConnectorReal();

		try {
			connector.start();
			Connection connection = connector.getConnection();

			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();

			CSVGenerator generator = new CSVGenerator(new File(
					"D:\\my docs\\sql_out\\" + tableName + ".csv"));
			generator.build(rs);
			generator.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			connector.close();
		}
		System.out.println("end :" + tableName);
	}
}
